package com.termine.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.LinkedList;
import java.util.List;

import javax.faces.bean.ApplicationScoped;
import javax.faces.bean.ManagedBean;

import com.termine.db.Connector;
import com.termine.entity.PersonalData;
import com.termine.entity.Service;
import com.termine.entity.ServiceSelectItem;

@ManagedBean
@ApplicationScoped
public class ServiceDAO {

	public void updateService(Service service) throws Exception {
		Connection connection = null;
		PreparedStatement statement = null;
		String statementString = "update SERVICE set S_NAME = ?, S_DESCRIPTION = ?, S_DURATION_IN_MIN = ?, "
				+ "S_BOOK_DEADLINE_IN_MIN = ?, S_CANCEL_DEADLINE_IN_MIN = ? "
				+ "where S_ID = ?";
		try {
			connection = Connector.getConnection();
			statement = connection.prepareStatement(statementString);
			statement.setString(1, service.getName());
			statement.setString(2, service.getDescription());
			statement.setInt(3, service.getDuration_in_min());
			statement.setInt(4, service.getBook_deadline_in_min());
			statement.setInt(5, service.getCancel_deadline_in_min());
			statement.setInt(6, service.getService_id());
			statement.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
			Connector.closeConnection(connection);
		}
	}

	public void addService(Service service, int providerId) throws Exception {
		Connection connection = null;
		PreparedStatement statement = null;
		String statementString = "insert into SERVICE "
				+ "(P_ID,S_NAME,S_DESCRIPTION,S_DURATION_IN_MIN,S_BOOK_DEADLINE_IN_MIN,S_CANCEL_DEADLINE_IN_MIN) "
				+ " values (?,?,?,?,?,?)";
		try {
			connection = Connector.getConnection();
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, providerId);
			statement.setString(2, service.getName());
			statement.setString(3, service.getDescription());
			statement.setInt(4, service.getDuration_in_min());
			statement.setInt(5, service.getBook_deadline_in_min());
			statement.setInt(6, service.getCancel_deadline_in_min());
			statement.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
			Connector.closeConnection(connection);
		}
	}

	public void deleteService(Service service) throws Exception {
		Connection connection = null;
		PreparedStatement statement = null;
		String statementString = "delete from SERVICE where S_ID = ?";
		try {
			connection = Connector.getConnection();
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, service.getService_id());
			statement.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
			Connector.closeConnection(connection);
		}
	}

	public List<Service> getAllServicesForProvider(PersonalData personalData)
			throws Exception {
		Connection connection = null;
		PreparedStatement statement = null;
		String statementString = "select * from SERVICE where P_ID = ?";
		try {
			connection = Connector.getConnection();
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, personalData.getProvider_id());
			ResultSet resultSet = statement.executeQuery();

			List<Service> result = new LinkedList<Service>();
			while (resultSet.next()) {
				Service service = new Service();
				Integer integer;
				String string;

				integer = new Integer(resultSet.getInt("S_ID"));
				if (!resultSet.wasNull()) {
					service.setService_id(integer);
				}
				integer = new Integer(resultSet.getInt("P_ID"));
				if (!resultSet.wasNull()) {
					service.setProvider_id(integer);
				}
				string = resultSet.getString("S_NAME");
				if (!resultSet.wasNull()) {
					service.setName(string);
				}
				string = resultSet.getString("S_DESCRIPTION");
				if (!resultSet.wasNull()) {
					service.setDescription(string);
				}
				integer = new Integer(resultSet.getInt("S_DURATION_IN_MIN"));
				if (!resultSet.wasNull()) {
					service.setDuration_in_min(integer);
				}
				integer = new Integer(
						resultSet.getInt("S_BOOK_DEADLINE_IN_MIN"));
				if (!resultSet.wasNull()) {
					service.setBook_deadline_in_min(integer);
				}
				integer = new Integer(
						resultSet.getInt("S_CANCEL_DEADLINE_IN_MIN"));
				if (!resultSet.wasNull()) {
					service.setCancel_deadline_in_min(integer);
				}
				result.add(service);
			}
			return result;
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
			Connector.closeConnection(connection);
		}
	}

	public List<ServiceSelectItem> getAllServices() throws Exception {
		Connection connection = null;
		PreparedStatement statement = null;
		String statementString = "select * from SERVICE s join PROVIDER p on s.P_ID = p.P_ID "
				+ "join PERSONAL_DATA pd on p.PD_ID = pd.PD_ID";
		try {
			connection = Connector.getConnection();
			statement = connection.prepareStatement(statementString);
			ResultSet resultSet = statement.executeQuery();

			List<ServiceSelectItem> result = null;
			if (!resultSet.isClosed()) {
				result = new LinkedList<ServiceSelectItem>();
				while (resultSet.next()) {
					ServiceSelectItem service = new ServiceSelectItem();
					Integer integer;
					String string;

					integer = new Integer(resultSet.getInt("S_ID"));
					if (!resultSet.wasNull()) {
						service.setServiceId(integer);
					}
					string = resultSet.getString("S_NAME");
					if (!resultSet.wasNull()) {
						service.setName(string);
					}
					string = resultSet.getString("S_DESCRIPTION");
					if (!resultSet.wasNull()) {
						service.setDescription(string);
					}
					integer = new Integer(resultSet.getInt("S_DURATION_IN_MIN"));
					if (!resultSet.wasNull()) {
						service.setDurationInMin(integer);
					}
					integer = new Integer(resultSet.getInt("S_BOOK_DEADLINE_IN_MIN"));
					if (!resultSet.wasNull()) {
						service.setBookDeadlineInMin(integer);
					}
					integer = new Integer(resultSet.getInt("S_CANCEL_DEADLINE_IN_MIN"));
					if (!resultSet.wasNull()) {
						service.setCancelDeadlineInMin(integer);
					}
					string = resultSet.getString("PD_FORENAME");
					if (!resultSet.wasNull()) {
						service.setForename(string);
					}
					string = resultSet.getString("PD_SIRNAME");
					if (!resultSet.wasNull()) {
						service.setSirname(string);
					}
					string = resultSet.getString("PD_PHONE_NUMBER");
					if (!resultSet.wasNull()) {
						service.setPhoneNumber(string);
					}
					string = resultSet.getString("PD_EMAIL");
					if (!resultSet.wasNull()) {
						service.setEmail(string);
					}
					result.add(service);
				}
			}
			return result;
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
			Connector.closeConnection(connection);
		}
	}

}
